package com.cqeec.action;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;

import com.cqeec.bean.ReaderInfo;
import com.cqeec.util.DBUtil;

/**
 * 
 * @author HuaL
 * @description 读者控制器
 */
public class ReaderAction {
	Connection con = null;
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	public ReaderAction() {
		con = DBUtil.getConnection();
	}

	/**
	 * 
	 * @param 读者对象
	 * @return 存放读者信息的二维数组
	 */
	public String[][] getReaderList(ReaderInfo info) {
		String sql = "";
		sql = "SELECT * FROM ReaderInfo where 1=1 ";
		if (!info.getReaderid().trim().equals("")) {
			sql = sql + " and readerid='" + info.getReaderid().trim() + "'";
		}
		if (!info.getReadername().trim().equals("")) {
			sql = sql + " and readername like '%" + info.getReadername().trim()
					+ "%'";
		}
		if (!info.getSex().trim().equals("")) {
			sql = sql + " and sex='" + info.getSex().trim() + "'";
		}
		
		if (!info.getStartDate().trim().equals("")) {
			sql = sql + " and birthday>='" + info.getStartDate().trim()
					+ "'";
		}
		if (!info.getEndDate().trim().equals("")) {
			sql = sql + " and birthday<='" + info.getEndDate().trim() + "'";
		}
		if (!info.getTel().trim().equals("")) {
			sql = sql + " and tel=" + info.getTel().trim();
		}
		ResultSet rs = null;
		String sn[][] = null;
		int row = 0;
		try {
			Statement stat = con.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stat.executeQuery(sql);
			if (rs.last()) {
				row = rs.getRow();
			}
			if (row != 0) {
				sn = new String[row][5];
				rs.first();
				rs.previous();
				int i = 0;
				while (rs.next()) {
					sn[i][0] = rs.getString("readerid");
					sn[i][1] = rs.getString("readername");
					sn[i][2] = rs.getString("sex");
					if (rs.getDate("birthday") != null){
						sn[i][3] = sdf.format(rs.getDate("birthday"));
					}else{
						sn[i][3] = rs.getString("birthday");
					}
					sn[i][4] = rs.getString("tel");
					i++;
				}
				rs.close();
				stat.close();
			}
		} catch (Exception e) {
		}
		return sn;
	}
	
	/**
	 * 
	 * @param info 读者对象
	 * @return  添加成功返回true，否则返回false
	 */
	public boolean add(ReaderInfo info) {
		String sql = "Insert into ReaderInfo(readerid,readername,sex,birthday,tel) values(?,?,?,?,?)";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, info.getReaderid().trim());
			pstm.setString(2, info.getReadername().trim());
			pstm.setString(3, info.getSex());
			pstm.setString(4, info.getBirthday().trim());
			pstm.setString(5, info.getTel().trim());
			pstm.executeUpdate();
			pstm.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		}
	}
	/**
	 * 
	 * @param info 读者对象
	 * @return  修改成功返回true，否则返回false
	 */
	public boolean update(ReaderInfo info) {
		String sql = "UPDATE ReaderInfo set readername=?,sex=?,birthday=?,tel=? where readerid=?";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, info.getReadername().trim());
			pstm.setString(2, info.getSex());
			pstm.setString(3, info.getBirthday().trim());
			pstm.setString(4, info.getTel().trim());
			pstm.setString(5, info.getReaderid().trim());
			pstm.executeUpdate();
			pstm.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		}
	}
	/**
	 * 
	 * @param readerid 读者编号
	 * @return  删除成功返回true，否则返回false
	 */
	public boolean del(String readerid) {
		String sql = "Delete ReaderInfo where readerid=?";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, readerid);
			pstm.executeUpdate();
			pstm.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		}
	}
	/**
	 * 
	 * @param readerid 读者编号
	 * @return 添加读者时若存在返回false，否则返回true
	 */
	public boolean beforeadd(String readerid){
		String sql = "Select count(*) from ReaderInfo where readerid=?";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, readerid);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()){
				int count = rs.getInt(1);
				if (count == 0){
					return true;
				}else{
					return false;
				}
			}else{
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		}
	}
	/**
	 * 
	 * @param readerid 读者编号
	 * @return 删除读者时，若在借阅信息表中存在返回false，否则返回true
	 */
	public boolean beforedel(String readerid){
		String sql = "select count(*) from LendingInfo where readerid=?";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, readerid);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()){
				int count = rs.getInt(1);
				if (count == 0){
					return true;
				}else{
					return false;
				}
			}else{
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return false;
		}
	}
	
	/**
	 * 
	 * @param readerid 读者编号
	 * @return 读者信息对象
	 */
	public ReaderInfo getReaderInfoByID(String readerid){
		ReaderInfo readerInfo = null;
		String sql = "select * from ReaderInfo where readerid=?";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, readerid);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()){
				readerInfo = new ReaderInfo();
				readerInfo.setReadername(rs.getString("readerName"));
				readerInfo.setBirthday(sdf.format(rs.getDate("birthday")));
				readerInfo.setSex(rs.getString("sex"));
				readerInfo.setTel(rs.getString("tel"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
		}
		return readerInfo;
	}
	
}
